{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6e7def8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_SparkSQL_讯捷集团政策(保卫)分析.ipynb\n",
    "# Create By GF 2023-12-25 13:37"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e83f1251",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import IntegerType, DateType\n",
    "from pyspark.sql.window import Window\n",
    "from pyspark.sql.functions import col, desc, format_string, lit, row_number, count, when"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2e2a26fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PySpark 创建 Spark 会话(连接)。\n",
    "# --------------------------------------------------\n",
    "spark = SparkSession.builder.appName('Basic').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f41a86f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Policy_Catalog = spark.read.option(\"header\",\"true\") \\\n",
    "                              .option(\"encoding\", \"gbk\") \\\n",
    "                              .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_保卫政策门店目录_2023-02-17至2023-12-22.csv\")\n",
    "DF_Assault_Data   = spark.read.option(\"header\",\"true\") \\\n",
    "                              .option(\"encoding\", \"gbk\") \\\n",
    "                              .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_突击数据_集团1加5精英突击前后对比_2023-02-08至2023-12-13.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dd1d0fe1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保卫政策门店目录:  ['门店名称', '开始时间(保卫)', '结束时间(保卫)']\n",
      "1加5突击前后数据:  ['部门', '门店简称', '门店类别', '突击时间', '开始日期', '结束日期', '突击天数', '队长', '门店全称', '项目', '突击中日均(平时)', '突击中日均(周末)', '突击中日均(日均)', '突击前日均(平时)', '突击前日均(周末)', '突击前日均(日均)', '实际增长', '相对增长', '相对涨幅', '门店涨幅', '体系突击前', '体系突击后', '大盘涨幅', '体系突击前(所有门店)', '体系突击后(所有门店)']\n"
     ]
    }
   ],
   "source": [
    "# 字段列表\n",
    "# --------------------------------------------------\n",
    "print(\"保卫政策门店目录: \", DF_Policy_Catalog.columns)\n",
    "print(\"1加5突击前后数据: \", DF_Assault_Data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0523b517",
   "metadata": {},
   "outputs": [],
   "source": [
    "DF_Policy_Catalog = DF_Policy_Catalog.withColumn(\"ROWSNUM\", row_number().over(Window.orderBy(\"开始时间(保卫)\")))\n",
    "DF_Assault_Data   = DF_Assault_Data.withColumn(\"ROWSNUM\", row_number().over(Window.orderBy(\"开始日期\")))\n",
    "# --------------------------------------------------\n",
    "DF_Policy_Catalog = DF_Policy_Catalog.withColumn(\"开始时间(突击)\", lit(\"Null\"))\n",
    "DF_Policy_Catalog = DF_Policy_Catalog.withColumn(\"结束时间(突击)\", lit(\"Null\"))\n",
    "# --------------------------------------------------\n",
    "# SparkSQL 注册表名\n",
    "#personDF.registerTempTable(\"\") # -> 会过期的临时表。\n",
    "#personDF.createOrReplaceGlobalTempView(\"\") # -> 创建全局的,跨 SparkSession 也可用, 生命周期较长。\n",
    "# --------------------------------------------------\n",
    "DF_Policy_Catalog.createOrReplaceTempView(\"TB_Policy_Catalog\") #-> 创建临时的,当前 SparkSession 可用。\n",
    "DF_Assault_Data.createOrReplaceTempView(\"TB_Assault_Data\")\n",
    "# --------------------------------------------------\n",
    "DF_Policy_Catalog_Max_Rows_Num = spark.sql(\"SELECT MAX(ROWSNUM) FROM TB_Policy_Catalog\").collect()[0][\"max(ROWSNUM)\"]\n",
    "DF_Assault_Data_Max_Rows_Num = spark.sql(\"SELECT MAX(ROWSNUM) FROM TB_Assault_Data\").collect()[0][\"max(ROWSNUM)\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "943debb6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------------+--------------+--------------+--------------+--------------+\n",
      "|ROWSNUM|                门店名称|开始时间(保卫)|结束时间(保卫)|开始时间(突击)|结束时间(突击)|\n",
      "+-------+------------------------+--------------+--------------+--------------+--------------+\n",
      "|      1|四川青白江青江东路形象店|    2023-02-17|    2023-02-26|          Null|          Null|\n",
      "|      2|      四川邛崃正大形象店|    2023-02-17|    2023-03-03|          Null|          Null|\n",
      "|      3|    四川温江大南街形象店|    2023-02-17|    2023-03-08|          Null|          Null|\n",
      "|      4|    四川郫县犀浦形象二店|    2023-02-17|    2023-03-08|          Null|          Null|\n",
      "|      5|      四川郫县犀浦华为店|    2023-02-17|    2023-03-08|          Null|          Null|\n",
      "|      6|    四川成都蜀汉路形象店|    2023-02-17|    2023-03-08|          Null|          Null|\n",
      "|      7|    四川新都新中路形象店|    2023-02-18|    2023-02-26|          Null|          Null|\n",
      "|      8|  四川新都桂湖东路形象店|    2023-02-18|    2023-02-26|          Null|          Null|\n",
      "|      9|    四川双流淳化街形象店|    2023-02-18|    2023-02-28|          Null|          Null|\n",
      "|     10|      四川眉山万达形象店|    2023-02-18|    2023-02-28|          Null|          Null|\n",
      "|     11|      陕西咸阳团结形象店|    2023-02-19|    2023-02-28|          Null|          Null|\n",
      "|     12|  陕西西安城市立方形象店|    2023-02-19|    2023-02-28|          Null|          Null|\n",
      "|     13|  陕西宝鸡电子大厦形象店|    2023-02-19|    2023-02-28|          Null|          Null|\n",
      "|     14|      四川成都财富形象店|    2023-02-19|    2023-02-28|          Null|          Null|\n",
      "|     15|    陕西西安南大街形象店|    2023-02-19|    2023-02-28|          Null|          Null|\n",
      "|     16|    四川成都蜀汉路形象店|    2023-02-27|    2023-03-08|          Null|          Null|\n",
      "|     17|      四川郫县犀浦华为店|    2023-02-27|    2023-03-08|          Null|          Null|\n",
      "|     18|      四川成都沙湾形象店|    2023-02-28|    2023-03-09|          Null|          Null|\n",
      "|     19|  四川成都沙湾华为plus店|    2023-02-28|    2023-03-09|          Null|          Null|\n",
      "|     20|    四川龙泉建新巷形象店|    2023-02-28|    2023-03-09|          Null|          Null|\n",
      "+-------+------------------------+--------------+--------------+--------------+--------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+-------+------------------+----------+----------+----+----------------+----------------+\n",
      "|ROWSNUM|          门店全称|  开始日期|  结束日期|项目|突击中日均(平时)|突击中日均(周末)|\n",
      "+-------+------------------+----------+----------+----+----------------+----------------+\n",
      "|      1|              null|      null|      null|主机|        364.7109|           484.5|\n",
      "|      2|              null|      null|      null|毛利|     928951.8473|    1236873.4734|\n",
      "|      3|              null|      null|      null|主机|        367.4677|          455.75|\n",
      "|      4|              null|      null|      null|毛利|     865722.2401|     1100834.926|\n",
      "|      5|              null|      null|      null|主机|        386.1336|        522.5833|\n",
      "|      6|              null|      null|      null|毛利|     943681.8873|     1280405.542|\n",
      "|      7|              null|      null|      null|主机|        357.3634|           455.0|\n",
      "|      8|              null|      null|      null|毛利|     866726.1143|    1096025.7586|\n",
      "|      9|              null|      null|      null|主机|        366.6226|          496.25|\n",
      "|     10|              null|      null|      null|毛利|     821871.5214|    1068449.9003|\n",
      "|     11|              null|      null|      null|主机|        366.9032|          464.75|\n",
      "|     12|              null|      null|      null|毛利|     856099.3304|    1024007.0403|\n",
      "|     13|              null|      null|      null|主机|        323.4332|        389.0833|\n",
      "|     14|              null|      null|      null|毛利|     832194.8145|     931332.2752|\n",
      "|     15|              null|      null|      null|主机|        193.3332|        284.5833|\n",
      "|     16|              null|      null|      null|毛利|     451438.7677|     639982.9843|\n",
      "|     17|              null|      null|      null|主机|        334.2858|        434.5833|\n",
      "|     18|              null|      null|      null|毛利|     845088.8527|    1151731.6234|\n",
      "|     19|四川双流万达形象店|2023-02-08|2023-02-16|主机|          7.1429|             9.5|\n",
      "|     20|四川双流万达形象店|2023-02-08|2023-02-16|毛利|      11900.1348|      17122.6859|\n",
      "+-------+------------------+----------+----------+----+----------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "DF_Policy_Catalog.select([\"ROWSNUM\", \"门店名称\", \"开始时间(保卫)\", \"结束时间(保卫)\", \"开始时间(突击)\", \"结束时间(突击)\"]).show()\n",
    "DF_Assault_Data.select([\"ROWSNUM\", \"门店全称\", \"开始日期\", \"结束日期\", \"项目\", \"突击中日均(平时)\", \"突击中日均(周末)\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d02c36bb",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row(门店名称='四川青白江青江东路形象店', 开始时间(保卫)='2023-02-17', 开始时间(突击)='Null', 结束时间(突击)='Null')\n"
     ]
    }
   ],
   "source": [
    "for Left_ROWSNUM in range(1, (DF_Policy_Catalog_Max_Rows_Num + 1)):\n",
    "    Result1 = spark.sql(\"\"\"SELECT\n",
    "                              `门店名称`,\n",
    "                              `开始时间(保卫)`,\n",
    "                              `开始时间(突击)`,\n",
    "                              `结束时间(突击)`\n",
    "                           FROM\n",
    "                               TB_Policy_Catalog\n",
    "                           WHERE\n",
    "                               ROWSNUM = %d;\"\"\" % Left_ROWSNUM)\n",
    "    # ------------------------------------------\n",
    "    Left_Store_Name       = Result1.collect()[0][\"门店名称\"]\n",
    "    Left_Bgn_Date_Secure  = Result1.collect()[0][\"开始时间(保卫)\"]\n",
    "    Left_Bgn_Date_Assault = Result1.collect()[0][\"开始时间(突击)\"]\n",
    "    Left_End_Date_Assault = Result1.collect()[0][\"结束时间(突击)\"]\n",
    "    for Right_ROWSNUM in range(1, (DF_Assault_Data_Max_Rows_Num + 1)):\n",
    "        Result2 = spark.sql(\"\"\"SELECT\n",
    "                                  `门店全称`,\n",
    "                                  `开始日期`,\n",
    "                                  `结束日期`\n",
    "                               FROM\n",
    "                                   TB_Assault_Data\n",
    "                               WHERE\n",
    "                                   ROWSNUM = %d;\"\"\" % Right_ROWSNUM)\n",
    "        Right_Store_Name       = Result2.collect()[0][\"门店全称\"]\n",
    "        Right_Bgn_Date_Assault = Result2.collect()[0][\"开始日期\"]\n",
    "        Right_End_Date_Assault = Result2.collect()[0][\"结束日期\"]\n",
    "        # ------------------------------------------\n",
    "        if Left_Store_Name == Right_Store_Name:\n",
    "            Condition_1 = (col(\"ROWSNUM\") == Left_ROWSNUM) & (col(\"开始时间(突击)\") != \"Null\")\n",
    "            Condition_2 = (col(\"ROWSNUM\") == Left_ROWSNUM) & (col(\"结束时间(突击)\") != \"Null\")\n",
    "            # --------------------------------------\n",
    "            DF_Policy_Catalog = DF_Policy_Catalog.withColumn(\"开始时间(突击)\", when(Condition_1, Right_Bgn_Date_Assault))\n",
    "            DF_Policy_Catalog = DF_Policy_Catalog.withColumn(\"结束时间(突击)\", when(Condition_2, Right_End_Date_Assault))\n",
    "            #continue\n",
    "        print(Result1.collect()[0])\n",
    "        break\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e07ea1aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+--------------+--------------+-------+--------------+--------------+\n",
      "|                门店名称|开始时间(保卫)|结束时间(保卫)|ROWSNUM|开始时间(突击)|结束时间(突击)|\n",
      "+------------------------+--------------+--------------+-------+--------------+--------------+\n",
      "|四川青白江青江东路形象店|    2023-02-17|    2023-02-26|      1|          null|          null|\n",
      "|      四川邛崃正大形象店|    2023-02-17|    2023-03-03|      2|          null|          null|\n",
      "|    四川温江大南街形象店|    2023-02-17|    2023-03-08|      3|          null|          null|\n",
      "|    四川郫县犀浦形象二店|    2023-02-17|    2023-03-08|      4|          null|          null|\n",
      "|      四川郫县犀浦华为店|    2023-02-17|    2023-03-08|      5|          null|          null|\n",
      "|    四川成都蜀汉路形象店|    2023-02-17|    2023-03-08|      6|          null|          null|\n",
      "|    四川新都新中路形象店|    2023-02-18|    2023-02-26|      7|          null|          null|\n",
      "|  四川新都桂湖东路形象店|    2023-02-18|    2023-02-26|      8|          null|          null|\n",
      "|    四川双流淳化街形象店|    2023-02-18|    2023-02-28|      9|          null|          null|\n",
      "|      四川眉山万达形象店|    2023-02-18|    2023-02-28|     10|          null|          null|\n",
      "|      陕西咸阳团结形象店|    2023-02-19|    2023-02-28|     11|          null|          null|\n",
      "|  陕西西安城市立方形象店|    2023-02-19|    2023-02-28|     12|          null|          null|\n",
      "|  陕西宝鸡电子大厦形象店|    2023-02-19|    2023-02-28|     13|          null|          null|\n",
      "|      四川成都财富形象店|    2023-02-19|    2023-02-28|     14|          null|          null|\n",
      "|    陕西西安南大街形象店|    2023-02-19|    2023-02-28|     15|          null|          null|\n",
      "|    四川成都蜀汉路形象店|    2023-02-27|    2023-03-08|     16|          null|          null|\n",
      "|      四川郫县犀浦华为店|    2023-02-27|    2023-03-08|     17|          null|          null|\n",
      "|      四川成都沙湾形象店|    2023-02-28|    2023-03-09|     18|          null|          null|\n",
      "|  四川成都沙湾华为plus店|    2023-02-28|    2023-03-09|     19|          null|          null|\n",
      "|    四川龙泉建新巷形象店|    2023-02-28|    2023-03-09|     20|          null|          null|\n",
      "+------------------------+--------------+--------------+-------+--------------+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "DF_Policy_Catalog.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7c920e9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
